﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using saigonbook.Biz;
using System.Data;

namespace saigonbook.Dal
{
    class DAL_user
    {
        public static List<BIZ_user> Select_user()
        {
            DataProvider db = new DataProvider();
            DataTable dt = db.GetData("Select * from [USER]");
            List<BIZ_user> list = new List<BIZ_user>();
            foreach (DataRow row in dt.Rows)
            {
                BIZ_user user = new BIZ_user(row[0].ToString(), row[1].ToString(), 
                            DAL_user.GetPhanQuyenById((int)row[2]), row[3].ToString(), 
                            (DateTime)row[4], (int)row[5], row[6].ToString(), row[7].ToString(), row[8].ToString());
                list.Add(user);
            }
            return list;
        }

        public static BIZ_phanquyen GetPhanQuyenById(int maphanquyen)
        {
            DataProvider db = new DataProvider();
            DataTable dt = db.GetData("Select * from phanquyen where maphanquyen='" + maphanquyen + "'");
            BIZ_phanquyen result = null;
            foreach (DataRow row in dt.Rows)
            {
                result = new BIZ_phanquyen((int)row[0], row[1].ToString());
            }
            return result;
        }

        public static void Them_user(BIZ_user dto)
        {
            DataProvider db = new DataProvider();
            db.ExecuteQuery("Insert into [user](username,password,maphanquyen,hoten,ngaysinh,gioitinh,sodienthoai,diachi,email) VALUES (N'" + dto.username + "',N'" + dto.password + "',N'" + dto.phanquyen.maphanquyen + "',N'" + dto.hoten + "',N'" + dto.ngaysinh.Date.ToString("MM-dd-yyyy") + "',N'" + dto.gioitinh + "',N'" + dto.sodienthoai + "',N'" + dto.diachi + "',N'" + dto.email + "')");
        }
        public static void Sua_user(BIZ_user dto)
        {
            DataProvider db = new DataProvider();
            db.ExecuteQuery("Update [user] set password=N'" + dto.password + "',maphanquyen=N'" + dto.phanquyen.maphanquyen + "',hoten=N'" + dto.hoten + "',ngaysinh=N'" + dto.ngaysinh.Date.ToString("MM-dd-yyyy") + "',gioitinh=N'" + dto.gioitinh + "',sodienthoai=N'" + dto.sodienthoai + "',diachi=N'" + dto.diachi + "',email=N'" + dto.email + "' where username= '" + dto.username + "'");
        }
    }
}
